#include "head.h"

sqlite3 *db = NULL;  //数据库句柄

int do_login(int sockfd, MSG *pbuf)
{
	char **resultp;
    int n_row, n_cloum;
	char *errmsg;
	int i;
	
	int flag = 0; 			//操作标志位
	MSG smsg = {0};			//发送结构体
	
	printf("recv:name:%s;passwd:%s\n",pbuf->name,pbuf->passwd);
	
	/*查表admin:判断是不是管理员*/
    if (sqlite3_get_table(db, "select *from admin", &resultp, &n_row, &n_cloum, &errmsg) != SQLITE_OK)//读数据库admin表
    {
        printf("err_admin:%s\n", errmsg);
		smsg.ret = ERROR;
		printf("ret:%d\n",smsg.ret);
		send(sockfd, &smsg, sizeof(MSG), 0);
		return 0;
    }
    for (i = 1; i < (n_row+1); i++)
    {
		/*管理员表格式为：Name  Passwd*/
		//printf("%s, %s\n", resultp[n_cloum*i], resultp[n_cloum*i+1]);
        if(!strcmp(pbuf->name, resultp[n_cloum*i])&&!strcmp(pbuf->passwd, resultp[n_cloum*i+1])){
			flag = 1;
			printf("%s is login!\n",pbuf->name);
		}
    }
	
	/*查表staff:判断是不是员工*/
	if (sqlite3_get_table(db, "select *from staff", &resultp, &n_row, &n_cloum, &errmsg) != SQLITE_OK)//读数据库staff表
    {
        printf("err_staff:%s\n", errmsg);
		smsg.ret = ERROR;
		printf("ret:%d\n",smsg.ret);
		send(sockfd, &smsg, sizeof(MSG), 0);
		return 0;
    }
    for (i = 1; i < (n_row+1); i++)
    {
		/*员工表格式为：ID  Name  Passwd  Age  Address  Salary*/
		//printf("%s, %s\n", resultp[n_cloum*i+1], resultp[n_cloum*i+2]);
        if(!strcmp(pbuf->name, resultp[n_cloum*i+1])&&!strcmp(pbuf->passwd, resultp[n_cloum*i+2])){
			flag = 2;
		}
    }
	
	//应答操作结果
	switch(flag) {
		case 1:  	//ADMIN
			puts("admin");
			smsg.ret = ADMIN;
			printf("ret:%d\n",smsg.ret);
			send(sockfd, &smsg, sizeof(MSG), 0);
			break;
		case 2:		//STAFF
			puts("staff");
			smsg.ret = STAFF;
			printf("ret:%d\n",smsg.ret);
			send(sockfd, &smsg, sizeof(MSG), 0);
			break;
		default:  	//ERROR
			puts("error");
			smsg.ret = ERROR;
			printf("ret:%d\n",smsg.ret);
			send(sockfd, &smsg, sizeof(MSG), 0);
			break;
	}
	return 1;
}

int do_staff_change_passwd(int sockfd, MSG *pbuf)
{
	char **resultp;
    int n_row, n_cloum;
	char *errmsg;
	int i,j;
	char cmd[256];
	int flag = 0; 			//操作标志位
	MSG smsg = {0};			//发送结构体
	printf("recv:name:%s\n", pbuf->info.name);
	
	/*查表staff*/
	if (sqlite3_get_table(db, "select *from staff", &resultp, &n_row, &n_cloum, &errmsg) != SQLITE_OK)//读数据库staff表
    {
        printf("err_staff:%s\n", errmsg);
    }
    for (i = 1; i < (n_row+1); i++)  
    {
		/*员工表格式为：ID  Name  Passwd  Age  Address  Salary*/
        if(!strcmp( pbuf->info.name, resultp[n_cloum*i+1])){
			printf("It is %s\n", resultp[n_cloum*i+1]);
			/*	实例：
					更新id=6的客户地址：
						update company set address = 'texas' where id = 6;

					如果您想修改 company 表中 address 和 salary 列的所有值，则不需要使用 where 子句，update 查询如下：
						update company set address = 'texas', salary = 20000.00      */
			sprintf(cmd, "update staff set pass = '%s' where name = '%s';",pbuf->data,pbuf->info.name);// 拼接
			printf("cmd = %s\n", cmd);
	
			if(sqlite3_exec(db, cmd,  NULL,  NULL, &errmsg) !=  SQLITE_OK){
				printf("error:%s\n",  errmsg);
				smsg.ret = 0;
				printf("ret:%d\n",smsg.ret);
				send(sockfd, &smsg, sizeof(MSG), 0);
				return 0;
			}
			puts("Change is ok");
			printf("pass:%s\n",resultp[n_cloum*i+2]);
			smsg.ret = 1;
			printf("ret:%d\n",smsg.ret);
			send(sockfd, &smsg, sizeof(MSG), 0);
			return 1;
		}
    }
}

int do_staff_query(int sockfd, MSG *pbuf)
{
	char **resultp;
	int n_row, n_cloum;
	char *errmsg;
	int i,j;

	MSG smsg = {0};			//发送结构体
	printf("recv:name:%s\n", pbuf->info.name);
	
	if (sqlite3_get_table(db, "select *from staff", &resultp, &n_row, &n_cloum, &errmsg) != SQLITE_OK)//读数据库staff表
    {
        printf("err_staff:%s\n", errmsg);
    }
    for (i = 1; i < (n_row+1); i++)  
    {
		/*员工表格式为：ID  Name  Passwd  Age  Address  Salary*/
        if(!strcmp(pbuf->info.name, resultp[n_cloum*i+1])){
			printf("It is %s\n", resultp[n_cloum*i+1]);
			sprintf(smsg.data,"date:\n  id:%s;  name:%s;  age:%s;  addr:%s;  salary:%s\n",
					resultp[n_cloum*i+0],resultp[n_cloum*i+1],resultp[n_cloum*i+3],resultp[n_cloum*i+4],resultp[n_cloum*i+5]);
			smsg.ret = 2;
			send(sockfd, &smsg, sizeof(MSG), 0);
			return 1;
		}
    }
	
	smsg.ret = 0;
	send(sockfd, &smsg, sizeof(MSG), 0);
	return 0;   
}

int do_admin_query(int sockfd, MSG *pbuf)
{
	char **resultp;
    int n_row, n_cloum;
	char *errmsg;
	int i,j;
	
	int flag = 0; 			//操作标志位
	MSG smsg = {0};			//发送结构体
	printf("recv:name:%s\n",  pbuf->info.name);
	
	/*查表admin*/
	if (sqlite3_get_table(db, "select *from admin", &resultp, &n_row, &n_cloum, &errmsg) != SQLITE_OK)//读数据库admin表
    {
        printf("err_admin:%s\n", errmsg);
		smsg.ret = 0;
		send(sockfd, &smsg, sizeof(MSG), 0);
		return 0;
    }
    for (i = 1; i < (n_row+1); i++)
    {
		/*管理员表格式为：Name  Passwd*/
        if(!strcmp( pbuf->info.name, resultp[n_cloum*i])){
			printf("It is %s\n", resultp[n_cloum*i]);
			flag = 4;
			sprintf(smsg.data,"date:\n  id:%s;  name:%s;  age:%s;  addr:%s;  salary:%s\n",
										"0",resultp[n_cloum*i],"华清远见","28","99999");
			smsg.ret = 4;
			send(sockfd, &smsg, sizeof(MSG), 0);
			return 0;
		}
    }
	
	/*查表staff*/
	if (sqlite3_get_table(db, "select *from staff", &resultp, &n_row, &n_cloum, &errmsg) != SQLITE_OK)//读数据库staff表
    {
        printf("err_staff:%s\n", errmsg);
    }
    for (i = 1; i < (n_row+1); i++)  
    {
		/*员工表格式为：ID  Name  Passwd  Age  Address  Salary*/
        if(!strcmp( pbuf->info.name, resultp[n_cloum*i+1])){
			printf("It is %s\n", resultp[n_cloum*i+1]);
			flag = 4;
			sprintf(smsg.data,"date:\n  id:%s;  name:%s;  age:%s;  addr:%s;  salary:%s\n",
					resultp[n_cloum*i+0],resultp[n_cloum*i+1],resultp[n_cloum*i+3],resultp[n_cloum*i+4],resultp[n_cloum*i+5]);
			smsg.ret = 4;
			send(sockfd, &smsg, sizeof(MSG), 0);
			return 0;
		}
    }
	
	/*查询错误答复*/
	if(flag != 4){
		smsg.ret = 0;
		send(sockfd, &smsg, sizeof(MSG), 0);
		return 0;
	}
    
}
/* 删除*/
void do_admin_del(int sockfd, MSG *pbuf)
{	
	char *errmsg;
	MSG dmsg = {0};       //发送结构体
	char cmd[256];
	
	sprintf(cmd, "delete from staff where name = '%s'", pbuf->info.name);    //删除指令
	printf("cmd = %s\n", cmd);
	dmsg.ret = sqlite3_exec(db, cmd, NULL, NULL, &errmsg);
	if(dmsg.ret!= SQLITE_OK)
	{
		printf("do admin del err:%s\n", errmsg);
		dmsg.ret = ERROR;
		printf("ret:%d\n",dmsg.ret);
		send(sockfd, &dmsg, sizeof(MSG), 0);	
	}
	
	puts("do admin del ok");
	dmsg.ret = SUCCESS;
	printf("ret:%d\n",dmsg.ret);
	send(sockfd, &dmsg, sizeof(MSG), 0);
	
}

int do_admin_add(int sockfd, MSG *pbuf)
{
	char *errmsg;
	char cmd[256];
	MSG smsg = {0};			//发送结构体

	sprintf(cmd, "insert into staff values(%d, '%s', '%s', %d, '%s', %0.1f)",
			(pbuf->info.id), pbuf->info.name, pbuf->passwd, (pbuf->info.age), pbuf->info.addr, (pbuf->info.salary));// 拼接
	printf("cmd = %s\n", cmd);
	
	if(sqlite3_exec(db, cmd,  NULL,  NULL, &errmsg) !=  SQLITE_OK){
		printf("error:%s\n",  errmsg);
		smsg.ret = 0;
		printf("ret:%d\n",smsg.ret);
		send(sockfd, &smsg, sizeof(MSG), 0);
		return 0;
	}
	puts("Insert is ok");
	smsg.ret = 1;
	printf("ret:%d\n",smsg.ret);
	send(sockfd, &smsg, sizeof(MSG), 0);
	return 1;
}


int do_admin_change_staff(int sockfd, MSG *pbuf)
{
	char **resultp;
    int n_row, n_cloum;
	char *errmsg;
	int i,j;
	char cmd[256];
	
	MSG smsg = {0};			//发送结构体
	MSG rmsg = {0};			//接收结构体
	int flag =0;
	printf("recv:name:%s\n",  pbuf->data);
	
	/*查表staff*/
	if (sqlite3_get_table(db, "select *from staff", &resultp, &n_row, &n_cloum, &errmsg) != SQLITE_OK)//读数据库staff表
    {
        printf("err_staff:%s\n", errmsg);
    }
    for (i = 1; i < (n_row+1); i++)  
    {
		/*员工表格式为：ID  Name  Passwd  Age  Address  Salary*/
        if(!strcmp( pbuf->data, resultp[n_cloum*i+1])){
			flag = 1;
			printf("It is %s\n", resultp[n_cloum*i+1]);
			sprintf(smsg.data,"date:\n  id:%s;  name:%s;  passwd:***;  age:%s;  addr:%s;  salary:%s\n",
					resultp[n_cloum*i+0],resultp[n_cloum*i+1],resultp[n_cloum*i+3],resultp[n_cloum*i+4],resultp[n_cloum*i+5]);
			smsg.ret = 3;
			send(sockfd, &smsg, sizeof(MSG), 0);
		}
    }
	if(flag == 1){
		printf("It have %s", pbuf->data);
	}
	else{
		printf("It isn't have %s", pbuf->data);
		smsg.ret = 0;
		printf("ret:%d\n",smsg.ret);
		send(sockfd, &smsg, sizeof(MSG), 0);
		return 0;
	}
	if(recv(sockfd, &rmsg, sizeof(MSG), 0) == -1){//等待接收
		perror("recv");
		puts("Please input again!");
		smsg.ret = 0;
		printf("ret:%d\n",smsg.ret);
		send(sockfd, &smsg, sizeof(MSG), 0);
		return 0;
	}
	printf("It is %s\n", rmsg.info.name);	
	/*	实例：
		更新id=6的客户地址：
			update company set address = 'texas' where id = 6;
		如果您想修改 company 表中 address 和 salary 列的所有值，则不需要使用 where 子句，update 查询如下：
			update company set address = 'texas', salary = 20000.00      */
	sprintf(cmd, "update staff set id = %d, name = '%s', age = %d, address = '%s', salary = %0.1f where name = '%s';",
			rmsg.info.id, rmsg.info.name, rmsg.info.age, rmsg.info.addr, rmsg.info.salary, rmsg.info.name);// 拼接
	printf("cmd = %s\n", cmd);
	if(sqlite3_exec(db, cmd,  NULL,  NULL, &errmsg) !=  SQLITE_OK){
		printf("error:%s\n",  errmsg);
		smsg.ret = 0;
		printf("ret:%d\n",smsg.ret);
		send(sockfd, &smsg, sizeof(MSG), 0);
		return 0;
	}
	puts("Change is ok");
	smsg.ret = 3;
	printf("ret:%d\n",smsg.ret);
	send(sockfd, &smsg, sizeof(MSG), 0);
	return 1;	
}

void *handler(void * arg)
{
    MSG msg;
    int n;
    int connfd = (int)arg;
	printf("%d is login!\n", connfd);
	
    while(1)
    {
        n = recv(connfd, &msg, sizeof(MSG), 0);
        //printf("name = %s\n", msg.name);
        if(n == 0){  //客户端退出
			printf("%d is quit!\n", connfd);
            pthread_exit(NULL);
            close(connfd);
        }else if(n > 0){  //收到信息
            //printf("recv type = %d\n", msg.type);
            switch (msg.type) {
            case LOGIN:  //登录
                do_login(connfd, &msg);
                break;
            case STAFF_CHANGE_PASSWD:
                do_staff_change_passwd(connfd, &msg);
                break;
            case STAFF_QUERY:
                do_staff_query(connfd, &msg);
                break;
            case ADMIN_QUERY:
                do_admin_query(connfd, &msg);
                break;
            case ADMIN_DEL_STAFF:
                do_admin_del(connfd, &msg);
                break;
            case ADMIN_ADD_STAFF:
                do_admin_add(connfd, &msg);
                break;
			case ADMIN_CHANGE_STAFF:
				do_admin_change_staff(connfd, &msg);
            default:
                break;
            }
        }else{
            printf("abc");
            break;
        }
    }
    close(connfd);
    pthread_exit(NULL);
}

int main(int argc, char *argv[])
{
    int listenfd, connfd;
    struct sockaddr_in myaddr;

    if (argc < 3)
    {
        printf("Usage : %s <ip> <port>\n", argv[0]);
        exit(-1);
    }

    //打开数据库
    if(sqlite3_open(DATABASE, &db) < 0){
        printf("fail to sqlite3_open : %s\n", sqlite3_errmsg(db));
        return -1;
    }
	
    //创建服务器socket
    listenfd = socket(PF_INET, SOCK_STREAM, 0);
    if(listenfd < 0){
        perror("fail to socket");
        exit(-1);
    }
    bzero(&myaddr, sizeof(myaddr));
    myaddr.sin_family = PF_INET;
    myaddr.sin_port = htons(atoi(argv[2]));
    myaddr.sin_addr.s_addr = inet_addr(argv[1]);
    if (bind(listenfd, (SA *)&myaddr, sizeof(myaddr)) < 0)
    {
        perror("fail to bind");
        exit(-1);
    }

    // XXX int listen(int sockfd, int backlog);
    if (listen(listenfd, 5) < 0)
    {
        perror("fail to listen");
        exit(-1);
    }

    //接受客户端请求，创建子线程
    while(1){
        if((connfd = accept(listenfd, NULL, NULL)) < 0){
            perror("fail to accept");
            exit(-1);
        }
        pthread_t child_pthread;
        if(pthread_create(&child_pthread, NULL, handler,(void *)connfd) < 0)
        {
            perror("pthread_create");
            exit(1);
        }		
    }
}
